# -*- encoding: utf-8 -*-
from odoo import models, fields, api, _
from odoo.exceptions import UserError


class AccountDetailWizard(models.Model):
    _name = 'account.detail.wizard'
    _description = u'明细分类帐查询'
    _rec_name = 'name'

    name = fields.Char(default='明细分类帐查询', string='明细分类帐查询')
    date_type = fields.Selection([('this_year', u'本年'),
                                  ('this_month', u'本月'),
                                  ('today', u'今天'),
                                  ('other_month', u'其它期别')], u'日期范围', default='other_month')
    period_id = fields.Many2one('account.period', u'期别')
    start_date = fields.Date(u'起始日')
    end_date = fields.Date(u'截止日')
    from_account_id = fields.Many2one('cncw.account', u'起始会科')
    to_account_id = fields.Many2one('cncw.account', u'截止会科')
    line_ids = fields.One2many('account.detail', 'master_id', u'明细')

    @api.onchange('date_type')
    def onchange_date_type(self):
        if self.date_type == 'this_year':
            year = self.env['account.fiscalyear'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                          ('date_stop', '>=', fields.Date.context_today(self))],
                                                         limit=1)
            if year:
                self.start_date = year.date_start
                self.end_date = year.date_stop

        elif self.date_type == 'this_month':
            period_id = self.env['account.period'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                           ('date_stop', '>=', fields.Date.context_today(self))],
                                                          limit=1)
            if period_id:
                self.start_date = period_id.date_start
                self.end_date = period_id.date_stop
        elif self.date_type == 'today':
            self.start_date = fields.Date.context_today(self)
            self.end_date = fields.Date.context_today(self)
        elif self.date_type == 'other_month':
            pass

    @api.onchange('period_id')
    def onchange_period_id(self):
        if self.period_id:
            self.start_date = self.period_id.date_start
            self.end_date = self.period_id.date_stop

    @api.onchange('from_account_id')
    def onchange_from_account_id(self):
        if self.from_account_id:
            self.to_account_id = self.from_account_id.id

    def action_confirm(self):
        self.ensure_one()
        if not self.start_date:
            raise UserError(_(u'开始日期不可为空!'))

        if not self.end_date:
            raise UserError(_(u'截止日期不可为空!'))

        if not self.from_account_id:
            raise UserError(_(u'开始会科不可为空!'))

        if not self.to_account_id:
            raise UserError(_(u'截止会科不可为空!'))
        sql = """
            delete from account_detail where user_id=%s;
            delete from account_detail_wizard where create_uid=%s and id !=%s;
            select account_detail_settle('%s','%s','%s','%s',%s,%s);
            """ % (self._uid, self._uid, self.id,
                   self.start_date, self.end_date, self.from_account_id.code, self.to_account_id.code, self._uid,
                   self.id)
        self._cr.execute(sql)
        self._cr.commit()


class AccountDetail(models.Model):
    _name = 'account.detail'
    _description = u'明细分类帐查询'

    master_id = fields.Many2one('account.detail.wizard', u'主档', ondelete="cascade")
    sequence = fields.Integer(u'序', default=1)
    account_id = fields.Many2one('cncw.account', u'会科', ondelete="set null")
    name = fields.Char(u'凭证编号')
    account_voucher_id = fields.Many2one(comodel_name='account.voucher', string='会计凭证')
    date = fields.Date(u'凭证日期')
    line_name = fields.Char(u'摘要')
    line_sequence = fields.Integer(u'项次', default=1)
    begin_amount = fields.Float(u'期初', digits='Amount')
    debit = fields.Float(u'借方金额', digits='Amount')
    credit = fields.Float(u'贷方金额', digits='Amount')
    end_amount = fields.Float(u'期末', digits='Amount')
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="restrict")
    user_id = fields.Many2one('res.users', u'当前用户', ondelete="set null")
    duration = fields.Char(u'查询期间')

    def init(self):
        self._cr.execute("""
        create or replace function account_detail_settle(start_date_t timestamp without time zone, end_date_t timestamp without time zone,
                                      from_account_no character varying, to_account_no character varying, uid_t integer,
                                      master_id_t integer) returns void
    language plpgsql
as
$$
    -- 明细分类帐查询
--DROP FUNCTION account_detail_settle(timestamp,timestamp,character varying,character varying,integer,integer)
-- select account_detail_settle('2016-11-09','2016-11-09','11210000','11310000',1,2);
DECLARE
    period_date_start_t TIMESTAMP;
    period_t            INTEGER;
    begin_amount_t      NUMERIC := 0.0;
    debit_cridet_t      NUMERIC := 0.0;
    end_t               NUMERIC := 0.0;
    rec                 record;
    rec_account         record;
    seq                 INTEGER := 0;
    duration_t          CHARACTER VARYING;
BEGIN
    DELETE
    FROM account_detail
    WHERE master_id = master_id_t;

    SELECT ID
            ,
           date_start
    INTO period_t,
        period_date_start_t
    FROM account_period
    WHERE start_date_t BETWEEN date_start
              AND date_stop;
    duration_t := SUBSTRING(start_date_t :: CHARACTER VARYING, 1, 10) || ' ~ ' ||
                  SUBSTRING(end_date_t :: CHARACTER VARYING, 1, 10);
    FOR rec_account IN SELECT ID
                       FROM cncw_account
                       WHERE code BETWEEN from_account_no
                           AND to_account_no
                         AND sub_account_type <> 'view'
        loop
            seq := 0;
            DELETE
            FROM account_detail
            WHERE master_id = master_id_t
              AND account_id = rec_account.ID;
            begin_amount_t := 0.0;
            debit_cridet_t := 0.0;
            IF
                start_date_t <> period_date_start_t THEN
                SELECT SUM
                           (
                                   (COALESCE(lc_debit, 0) - COALESCE(lc_credit, 0)) *
                                   (CASE WHEN C.dc_type = 'D' THEN 1 ELSE - 1 END)
                           )
                INTO debit_cridet_t
                FROM account_voucher_line A
                         LEFT JOIN account_voucher b ON A.master_id = b.
                    ID
                         LEFT JOIN cncw_account C ON A.account_id = C.ID
                WHERE b.STATE IN ('draft', 'confirmed', 'audited')
                  AND period_date_start_t <= b.DATE
                  AND b.DATE < start_date_t --不含开始日
                  AND A.account_id = rec_account.ID;
            END IF;
--1.期初
            SELECT COALESCE
                       (BEGIN, 0)
            INTO begin_amount_t
            FROM account_balance
            WHERE period_id = period_t
              AND account_id = rec_account.ID;
            begin_amount_t := COALESCE(begin_amount_t, 0) + COALESCE(debit_cridet_t, 0);
            INSERT INTO account_detail (SEQUENCE, account_id, line_name, end_amount, user_id, duration, create_uid,
                                        create_date, master_id)
            VALUES (seq, rec_account.ID, '期初', begin_amount_t, uid_t, duration_t, uid_t, (now() AT TIME ZONE 'UTC'),
                    master_id_t);
--2.查询期间内
            seq := seq + 1;
            FOR rec IN SELECT A.account_id,
                              d.sub_account_id,
                              b.id                        as account_voucher_id,
                              b.name,
                              b.DATE,
                              A.SEQUENCE                  AS line_sequence,
                              A.NAME                      AS line_name,
                              SUM(COALESCE(lc_debit, 0))  AS debit,
                              SUM(COALESCE(lc_credit, 0)) AS credit,
                              C.dc_type
                       FROM account_voucher_line A
                                LEFT JOIN account_voucher b ON A.master_id = b.
                           ID
                                LEFT JOIN cncw_account C ON A.account_id = C.
                           ID
                                LEFT JOIN sub_account_line d ON A.ID = d.voucher_line_id
                       WHERE b.STATE IN ('draft', 'confirmed', 'audited')
                         AND b.DATE BETWEEN start_date_t
                           AND end_date_t
                         AND A.account_id = rec_account.ID
                       GROUP BY A.account_id,
                                d.sub_account_id,
                                b.id,
                                b.NAME,
                                b.DATE,
                                A.SEQUENCE,
                                A.NAME,
                                C.dc_type
                       ORDER BY b.DATE,
                                b.NAME,
                                b.id,
                                d.sub_account_id
                loop
                    end_t := COALESCE(begin_amount_t, 0) + (COALESCE(rec.debit, 0) - COALESCE(rec.credit, 0)) *
                                                           (CASE WHEN rec.dc_type = 'D' THEN 1 ELSE - 1 END);
                    INSERT INTO account_detail (SEQUENCE,
                                                account_id,
                                                sub_account_id,
                                                account_voucher_id,
                                                name,
                                                DATE,
                                                line_sequence,
                                                line_name,
                                                begin_amount,
                                                debit,
                                                credit,
                                                end_amount,
                                                user_id,
                                                duration,
                                                create_uid,
                                                create_date,
                                                master_id)
                    VALUES (seq,
                            rec.account_id,
                            rec.sub_account_id,
                            rec.account_voucher_id,
                            rec.name,
                            rec.DATE,
                            rec.line_sequence,
                            rec.line_name,
                            begin_amount_t,
                            rec.debit,
                            rec.credit,
                            end_t,
                            uid_t,
                            duration_t,
                            uid_t,
                            (now() AT TIME ZONE 'UTC'),
                            master_id_t);
                    seq := seq + 1;
                    begin_amount_t := end_t;

                END loop;

        END loop;

END;
$$;
        """)
